package cn.edu.hbwe.book.admin.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import cn.edu.hbwe.book.entity.Orders;
import cn.edu.hbwe.book.entity.User;
import cn.edu.hbwe.book.utils.C3P0Util;

public class OrderDao {

	private static QueryRunner run = new QueryRunner(C3P0Util.getDataSource());

	// 查找所有订单
	public List<Orders> findAllOrder() throws SQLException {
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id";
		return run.query(sql, new ResultSetHandler<List<Orders>>() {
			public List<Orders> handle(ResultSet rs) throws SQLException {
				// 创建订单集合
				List<Orders> orders = new ArrayList<Orders>();
				// 循环遍历订单和用户信息
				while (rs.next()) {
					Orders order = new Orders();
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrderTime(rs.getDate("orders.orderTime"));
					order.setPaystate(rs.getInt("orders.paystate"));
					order.setReceieverAddress(rs.getString("orders.receieverAddress"));
					order.setReceieverName(rs.getString("orders.receieverName"));
					order.setReceieverPhone(rs.getString("orders.receieverPhone"));
					orders.add(order);

					User user = new User();
					user.setId(rs.getInt("user.id"));
					user.setEmail(rs.getString("user.email"));
					user.setGender(rs.getString("user.gender"));
					user.setActiveCode(rs.getString("user.activecode"));
					user.setDescription(rs.getString("user.description"));
					user.setPassword(rs.getString("user.password"));
					user.setRegisTime(rs.getDate("user.regisTime"));
					user.setRole(rs.getString("user.role"));
					user.setState(rs.getInt("user.state"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setUsername(rs.getString("user.username"));
					order.setUser(user);
				}
				return orders;
			}
		});
	}

	//多条件查询
	public List<Orders> findOrderCondition(String id, String receieverName) throws SQLException {
		List<Object> objs = new ArrayList<Object>();
		//定义查询sql
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id ";
		//根据参数拼接sql语句
		if (id != null && id.trim().length() > 0) {
			sql += " and orders.id=?";
			objs.add(id);
		}
		if (receieverName != null && receieverName.trim().length() > 0) {
			sql += " and receieverName=?";
			objs.add(receieverName);
		}
		sql += " order by orders.user_id";
		return run.query(sql, new ResultSetHandler<List<Orders>>() {
			public List<Orders> handle(ResultSet rs) throws SQLException {
				List<Orders> orders = new ArrayList<Orders>();
				// 循环遍历出订单和用户信息
				while (rs.next()) {
					Orders order = new Orders();
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrderTime(rs.getDate("orders.orderTime"));
					order.setPaystate(rs.getInt("orders.paystate"));
					order.setReceieverAddress(rs.getString("orders.receieverAddress"));
					order.setReceieverName(rs.getString("orders.receieverName"));
					order.setReceieverPhone(rs.getString("orders.receieverPhone"));
					orders.add(order);
					User user = new User();
					user.setId(rs.getInt("user.id"));
					user.setEmail(rs.getString("user.email"));
					user.setGender(rs.getString("user.gender"));
					user.setActiveCode(rs.getString("user.activecode"));
					user.setDescription(rs.getString("user.description"));
					user.setPassword(rs.getString("user.password"));
					user.setRegisTime(rs.getDate("user.regisTime"));
					user.setRole(rs.getString("user.role"));
					user.setState(rs.getInt("user.state"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setUsername(rs.getString("user.username"));
					order.setUser(user);

				}

				return orders;
			}
		}, objs.toArray());
	}
}
